package zunge.twitterdel.typostat;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

import zunge.tools.DbConn;

public class ExtractResults {

	static String localHost = "localhost";
	static String localDb = "typos";
	static String localUser = "root";
	static DbConn localConn;
	
	static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0");
	
	public static void main(String args[]){
		getRealDelTweets("output.txt");
	}
	
	static void getRealDelTweets(String outputFile){
		try{
			localConn = new DbConn(localHost,localDb,localUser,null);
			
			String query = 
				"SELECT d1.* " +
				"FROM " +
						"deltweet d1 " +
					"LEFT JOIN " +
						"(SELECT " +
								"d.* " +
							"FROM " +
								"deltweet d, typotweets t " +
								"WHERE d.tweetID = t.oldTweetID " +
						") d2 " +
					"ON d1.tweetID = d2.tweetID " +
				"WHERE " +
					"d2.tweetID IS NULL ";
			
			ResultSet rs = localConn.executeQuery(query);
			
			BufferedWriter bw = new BufferedWriter(new FileWriter(outputFile));
			while(rs.next()){
				
				int id = rs.getInt("id");
				long userID = rs.getLong("userID");
				long tweetID = rs.getLong("tweetID");
				String actualTweet = rs.getString("actualTweet");
				String tweetDate = rs.getString("tweetDate");
				
				String[] tokens = actualTweet.split(" ");
				if(tokens.length > 3 && !actualTweet.startsWith("RT")){
					//bw.write(actualTweet+"\n");
					bw.write(id+"\t"+userID+"\t"+tweetID+"\t"+actualTweet+"\t"+tweetDate+"\n");
				}
			}
			bw.close();
			localConn.close();
			
			System.out.println("\nDone");
		}catch(Exception e){e.printStackTrace();}
	}
	
}
